package com.brother.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.brother.database.KenyaAddressDB;
import com.brother.model.LatLngPoint;

public class KenyaAddress {
	private static Logger log = Logger.getLogger(KenyaAddress.class);

	private static KenyaAddress instance = null;
	
	private KenyaAddress(){
		
	}
	
	public static KenyaAddress getInstance(){
		if(instance == null){
			instance = new KenyaAddress();
		}
		return instance;
	}
	
	
	public String getAddressByDB(float lat, float lng){
		if(!this.isIntesect(lat, lng)){
			return  null;
		}
		String sql = "select address from geo_point where (lat between "+(lat-0.0005)+"  and "+(lat+0.0005)+
		") and (lng between "+(lng-0.0005)+" and "+(lng+0.0005)+") ";
		
		ResultSet rs = KenyaAddressDB.query(sql);
		
		String address = "";
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				address = rs.getString("address");
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from geo_polyline where" +
		" Intersects(GeomFromText('MULTILINESTRING(("+lng+" "+(lat-0.0001)+", "+lng+" "+(lat+0.0002)+"))'),p_line)=1";
		rs = KenyaAddressDB.query(sql);
		//System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from `geo_polygon` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = KenyaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from `state_border_region` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = KenyaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		
		KenyaAddressDB.closeThreadConnection();
		return address;
		
	}
	

	public boolean isIntesect(float lat, float lng){
		try{
			String sql = "select address from `state_border_region` where" +
			" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1 limit 0,1";
			ResultSet rs = KenyaAddressDB.query(sql);
			if(rs.next() && rs.getString("address").endsWith("Kenya")
					|| rs.getString("address").endsWith("Uganda")){
				return true;
			}
		}catch(Exception e){
			log.error("", e);
		}finally{
			KenyaAddressDB.closeThreadConnection();
		}
		return false;
	}
	
}
